package com.dao;

import com.po.Envelope;
import com.utils.JDBCUnits;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EnvelopeDao {
    /**
     * 更改状态
     *
     * @param envelope_id
     * @param status
     */
    public static void changeStatus(int envelope_id, String status) {
        String sql = null;
        if ("open".equals(status)) {
            sql = "update envelope set envelope_state = 'close' where envelope_id = ?";
        } else {
            sql = "update envelope set envelope_state = 'open' where envelope_id=?";
        }
        Connection c = null;
        PreparedStatement ps = null;
        try {
            c = JDBCUnits.getConnectino();
            ps = c.prepareStatement(sql);
            ps.setInt(1, envelope_id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUnits.close(c, ps);
        }
    }

    /**
     * 搜索某个信封，因为信封可以重名，所以用一个List集合接受所有的这个名字的信封，让用户查看哪个信封是他想搜索的
     *
     * @param envelope_name 信封的名字
     * @return
     */
    public static List<Envelope> selectEnvelopes(String envelope_name) {
        List<Envelope> envelopes = new ArrayList<>();
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            c = JDBCUnits.getConnectino();
            String sql = "select  t.name as tname, t.sex as sex ,e.create_time as create_time, e.envelope_name as envelope_name,e.envelope_id as id from t_usr t join envelope e on t.id = e.user_id and e.envelope_name=? and e.envelope_state='open' ";
            ps = c.prepareStatement(sql);
            ps.setString(1, envelope_name);
            rs = ps.executeQuery();
            while (rs.next()) {
                Envelope envelope = new Envelope();
                envelope.setUser_name(rs.getString("tname"));
                envelope.setUser_sex(rs.getString("sex"));
                envelope.setCreate_time(rs.getDate("create_time"));
                envelope.setName(rs.getString("envelope_name"));
                envelope.setEnvelope_id(rs.getInt("id"));
                envelopes.add(envelope);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUnits.close(c, ps, rs);
        }
        return envelopes;
    }

    /**
     * 根据纸条的id获取纸条用户的名字
     *
     * @param note_id
     * @return
     */
    public static String getUserName(int note_id) {
        String note_username = null;
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            c = JDBCUnits.getConnectino();
            String sql = "select t.name as name from t_usr t join envelope e join note n on n.envelope_id = e.envelope_id and e.user_id = t.id and n.note_id = ?";
            ps = c.prepareStatement(sql);
            ps.setInt(1, note_id);
            rs = ps.executeQuery();
            if (rs.next()) {
                note_username = rs.getString("name");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUnits.close(c, ps, rs);
        }

        return note_username;
    }

    /**
     * 获取所有的公开的信封
     *
     * @param id 这个id是为了不用查看自己的信封
     * @return
     */
    public static List<Envelope> getOpenEnvelope(int id) {
        List<Envelope> list = new ArrayList<>();
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            c = JDBCUnits.getConnectino();
            String sql = "select e.envelope_name as ename , t.name as tname, t.sex as sex ,e.envelope_id as id from t_usr t join envelope e on t.id = e.user_id and e.envelope_state='open'and t.id!=? order by e.create_time desc";
            ps = c.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                Envelope envelope = new Envelope();
                envelope.setName(rs.getString("ename"));
                envelope.setUser_sex(rs.getString("sex"));
                envelope.setUser_name(rs.getString("tname"));
                envelope.setEnvelope_id(rs.getInt("id"));
                list.add(envelope);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUnits.close(c, ps, rs);
        }
        return list;
    }

    /**
     * 创建信封
     *
     * @param envelope_name 信封的名字
     * @param state         信封的状态
     * @param id
     * @return
     */
    public static int createEnvelope(String envelope_name, String state, int id) {
        int count = 0;
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            c = JDBCUnits.getConnectino();
            String sql = "insert into envelope(envelope_name,envelope_state,user_id,create_time) values(?,?,?,now())";
            ps = c.prepareStatement(sql);
            ps.setString(1, envelope_name);
            ps.setString(2, state);
            ps.setInt(3, id);

            count = ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUnits.close(c, ps, rs);
        }

        return count;
    }

    /**
     * 获取所有的信封
     * @param user_id
     * @param sql
     * @return
     */
    public static List<Envelope> getEnvelope(int user_id, String sql) {
        List<Envelope> myEnvelopes = new ArrayList<>();
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            c = JDBCUnits.getConnectino();
            ps = c.prepareStatement(sql);
            ps.setInt(1, user_id);
            rs = ps.executeQuery();

            while (rs.next()) {
                String name = rs.getString("envelope_name");
                String state = rs.getString("envelope_state");
                java.sql.Date create_time = rs.getDate("create_time");
                int envelope_id = rs.getInt("envelope_id");
                int total = Envelope.setTotalLike(envelope_id);

                Envelope envelope = new Envelope();
                envelope.setName(name);
                envelope.setCreate_time(create_time);
                envelope.setState(state);
                envelope.setEnvelope_id(envelope_id);
                envelope.setTotal_like(total);

                myEnvelopes.add(envelope);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUnits.close(c, ps, rs);
        }
        return myEnvelopes;
    }

    /**
     * 修改信封的名字
     * @param envelope_id
     * @param name
     */
    public static void modifyEnvelopeName(int envelope_id, String name){
        Connection c = null;
        PreparedStatement ps = null;
        try {
            c = JDBCUnits.getConnectino();
            String sql = "update envelope set envelope_name =? where envelope_id =?";
            ps = c.prepareStatement(sql);
            ps.setString(1,name);
            ps.setInt(2,envelope_id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUnits.close(c,ps);
        }
    }
}
